# -*- coding: utf-8 -*-
"""
Python与mysql的通信模块
"""
import pymysql
import datetime

def connect(host='127.0.0.1',user='hr', passwd='123456', db='stock',port=3306):
    '''连接mysql数据库'''
    conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset="utf8")
    return conn

def data_to_str(string):
    if not string:  # 如果是空字符串
        return None
    else:
        return str(string)
        

def df_into_db(conn,tb_name,df,types='insert'):
    '''将df导入mysql数据库，不需要特别处理日期列，相比oracle还是很方便的'''
    if len(df)==0:
        return None
    # 先将df全部转成字符串格式，主要是针对数值，df中的时间此时肯定是字符串类型
    df2 = df.applymap(data_to_str)
    # 
    cursor = conn.cursor()
    # 判断是否需要清空表
    if types.upper() in "TRUNCATE,DELETE":
        truncate_sql = "delete from " + tb_name
        cursor.execute(truncate_sql)
        cursor.execute('commit')
    
    # 将df转成list-tuple格式，才能导入数据库
    # df2=df
    param = []
    if len(df2)>1:
        for i in range(len(df2)):
            param.append(tuple(df2.iloc[i]))  # 转成list_tuple格式
    if len(df2)==1:
        param = [tuple(df2.iloc[0])]
    # 创建入库的sql
    sql = "insert into tb_name (cols) values (s_many_times) "
    cols = list(df2.columns)
    cols_string = ', '.join(cols)
    num = len(list(df2.columns))
    num = ['%s' for i in range(num)]
    num_string = ','.join(num)
    
    sql = sql.replace('tb_name',tb_name)
    sql = sql.replace('cols',cols_string)
    sql = sql.replace('s_many_times',num_string)
    # 入库
    cursor.executemany(sql, param)
    conn.commit()
    cursor.close()

def create_table_sql(tb_name, df):
    '''拼接create_table的sql语句。
    tb_name:表名
    df:要求有 column,types,context(注释) '''
    sql = "drop table tb_name;\ncreate table tb_name (\ncolumn_types_context\n)\n;\ncommit;\n"
    # 列名转大写
    df.columns = [col.upper() for col in df.columns]
    #
    df['TMP'] = df['COLUMN'] + ' ' + df['TYPES'] + " comment '" + df['CONTEXT'] + "'\n"  
    col_str = ','.join(df['TMP'])
    sql = sql.replace('column_types_context', col_str)
    sql = sql.replace('tb_name', tb_name)
    # 将建表的sql写到文件备份
    file = 'create_ ' + tb_name + ' .sql'
    with open(file, 'w', encoding='utf-8') as f:
        f.writelines(sql)
    return sql

def delete_today_data(conn,table,code='',date=''):
    '''删除表某天的数据，如果指定code，则删除指定股票的数据。如果date为空，则删除今天的数据.
    date:要求为20150603的格式'''
    cursor= conn.cursor()
    sql = "delete from limit_tb where 1=1 and_limit_code and_limit_date"
    # 替换tb
    sql = sql.replace('limit_tb',table)
    # 替换limit_code
    if code:
        limit_code="and code='"+code+"' "
    else:
        limit_code=""
    sql = sql.replace('and_limit_code',limit_code)
    # 替换limit_date
    if date:
        limit_date = "and dates=str_to_date('"+date+"','%Y%m%d')"  # mysql的日期表示法和python是一样的
    else:
        date = datetime.datetime.now().strftime('%Y%m%d')  # 如果指定时间，就删除今天的
        limit_date = "and dates=str_to_date('"+date+"','%Y%m%d')"
    sql = sql.replace('and_limit_date',limit_date)
    print('将执行sql：%s'%sql)
    # 删除数据
    cursor.execute(sql)
    cursor.execute("commit")
    cursor.close()
    # 将sql返回上层函数
    return sql


def main():
    pass


if __name__ == "__main__":
    main()


